#coding:utf8
import cherrypy
import datetime
import os
from ConnectMSSQL import ms

from head import *
from menu import *
from end import *

from utf8code import *
from xxtj import *

_js = """
        <script type="text/javascript">
            var sbbh;
            var sblx;
            $(function()
            {
                //开始隐藏层
                $(".title_div").hide();
                
                //点一个部门查询出该部门的所以pc使用信息
                $('.class_tr td a').click(function()
                {
                    window.location.href="/sbxx/serchSB?cxtj=" + $(this).text();
                });
                
                //高亮显示
                $(".table tr").hover
                (
                    function() { $(this).addClass("highlight"); },
                    function() { $(this).removeClass("highlight"); }
                );
                //单选框默认选中.变色.
                $('.table input[type="radio"]:checked').parents('tr').addClass('selected');
                
                //单击一行
                $('.table tr').click(function()
                {
                    //显示效果变化
                    $(this).siblings().removeClass('selected');
                    $(this).addClass('selected');
                    $(this).find('input[type="radio"]').attr('checked', 'checked');
                    sbbh=$(this).find('td[id="td_sbbh"]').text();
                    sblx=$(this).find('td[id="td_sblx"]').text();
                    
                    //==============ajax为div层添加数据====================
                    var xmlhttp;
                    xmlhttp=new XMLHttpRequest();//仅支持 IE7+, Firefox, Chrome, Opera, Safari
                    xmlhttp.onreadystatechange=function()
                    {
                      if (xmlhttp.readyState==4 && xmlhttp.status==200)
                        {
                            if(xmlhttp.responseText)
                            {
                                document.getElementById("myDiv").innerHTML=xmlhttp.responseText;
                                //显示层
                                $(".title_div").show();
                            }
                        }
                    }
                    xmlhttp.open("GET","/sbxx/showOtherInfo?sbbh="+sbbh+"&sblx="+sblx,true);
                    xmlhttp.send();
                    //======================================================
                });
                
                //鼠标离开这行数据
                $('.table tr').mouseleave(function()
                {
                    //隐藏层
                    $(".title_div").hide();
                });

                //双击一列
                $('.table tr').dblclick(function()
                {
                    if(sbbh)
                        window.open("/sbbgxx/serchBG?cxtj=" + sbbh);
                    else
                        alert("请选择一台设备");
                });
                
                //建档
                $("#newbut").click(function()
                {
                    window.open("/sbxz");
                });
                
                $("#delbut").click(function()
                {
                    if(sbbh)
                    {
                        var ret=confirm("确定删除编号为:" + sbbh + "的设备?此操作不可恢复!请谨慎操作!")
                        if(ret == false)
                            return;
                        else
                            //ajax为删除做检验，已变更的设备不可删除，不然会造成数据库错误
                            var xmlhttp;
                            if (window.XMLHttpRequest)
                            {// code for IE7+, Firefox, Chrome, Opera, Safari
                              xmlhttp=new XMLHttpRequest();
                            }
                            else
                            {// code for IE6, IE5
                              xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
                            }
                        
                            xmlhttp.onreadystatechange=function()
                            {
                              if (xmlhttp.readyState==4 && xmlhttp.status==200)
                                {
                                    if(xmlhttp.responseText == "no")
                                    {
                                        alert("删除失败!-_-|| 该设备已产生变更记录,不可删档!");
                                    }
                                    else
                                    {
                                        alert("删除成功!*^_^*");
                                        location.reload();
                                    }
                                }
                            }
                            xmlhttp.open("GET","/sbxx/delSB?sbbh="+sbbh,true);
                            xmlhttp.send();
                    }
                    else
                        alert("请选择一台设备");
                });
                
                //维护
                $("#maintainbut").click(function()
                {
                    if(sbbh)
                        window.open("/sbwh?sbbh=" + sbbh);
                    else
                        alert("请选择一台设备");
                });
                
                //设备类型查询
                $("#sblx_serch_selt").change(function()
                {
                    var sblx=$("#sblx_serch_selt option:selected").text();
                    if(sblx=="所有设备")
                        window.location.href="/sbxx/serchSB?cxtj=" + "";
                    else
                        window.location.href="/sbxx/serchSB?cxtj=" + sblx;
                });
                
                $("#printbut").click(function()
                {
                    if(sbbh)
                        window.open("/sbdak?sbbh=" + sbbh);
                    else
                        alert("请选择一台设备");
                });                 

                //变更记录维护
                $("#bgbut").click(function()
                {
                    window.open("/sbbgxx");
                });             

                //变更记录查询
                $("#bgserchbut").click(function()
                {
                    window.open("/sbbgjl");
                });
                
                $("#outbut").click(function()
                {
                    window.open("/sbxx/outSB");
                });
                
                $("#lybut").click(function()
                {
                    if(sbbh)
                        window.open("/sbbgxz?sbbh=" + sbbh);
                    else
                        alert("请选择一台设备");
                });
                
                $("#ghbut").click(function()
                {
                    if(sbbh)
                        window.open("/sbbgxz/sbgh?sbbh=" + sbbh);
                    else
                        alert("请选择一台设备");
                });
                
                $("#scrapbut").click(function()
                {
                    if(sbbh)
                        window.open("/sbbgxz/sbbf?sbbh=" + sbbh);
                    else
                        alert("请选择一台设备");
                });
            });
        </script>
        """
_js_noadmin = """
        <script type="text/javascript">
            $(function()
            {
                $("#newbut").hide();
                $("#delbut").hide();
                $("#maintainbut").hide();
                $("#lybut").hide();
                $("#ghbut").hide();
                $("#scrapbut").hide();
                $("#bgbut").hide();
            });
        </script>
"""

_top = """
    <form method="get" action="/sbxx/serchSB">
        <div id="div_operate">
            <input type="button" id="newbut" value="建档" />
            <input type="button" id="delbut" value="删档" />
            <input type="button" id="maintainbut" value="维护" />
            <input type="button" id="printbut" value="打印管理卡"  class="hint  hint--top" data-hint="Yeah, I am >:D"/>
                                查询条件：
            <td><select id="sblx_serch_selt"><option>所有设备</option>%s</select></td>
            <input type="checkbox" name="bfcheckbox"/>报废
            <input type="text" id="serchtext" name="cxtj" onfocus="hidevalue()" placeholder="设备编号、资产编号、设备名称、品牌型号、MAC地址、IP地址、在用部门、在用人" />
            <input type="submit" id="serchbut" value="查询" />
            <input type="button" id="outbut" value="导出全部设备" />
            <input type="button" id="lybut" value="领用" />
            <input type="button" id="ghbut" value="归还" />
            <input type="button" id="scrapbut" value="报废" />
            <input type="button" id="bgbut" value="变更记录维护" />
            <input type="button" id="bgserchbut" value="变更记录查询" />
            <input type="button" id="exitbut" onclick="exit()" value="关闭" />
        </div>
        
        <div class="title_div" id="myDiv">
        </div>
        
        <div id="div_data">
            <table width="auto" class="table">
              <tr class="class_tr_th">
                <th class="class_th" scope="col">√</th>
                <th class="class_th" scope="col">序号</th>
                <th class="class_th" scope="col">设备编号↓</th>
                <th class="class_th" scope="col">资产编号</th>
                <th class="class_th" scope="col">购买日期</th>
                <th class="class_th" scope="col">设备类型</th>
                <th class="class_th" scope="col">品牌型号</th>
                <th class="class_th" scope="col">设备名称(S/N)</th>
                <th class="class_th" scope="col">网络配置</th>
                <th class="class_th" scope="col">在用部门</th>
                <th class="class_th" scope="col">在用人</th>
              </tr>
          """
           
_end = """
            </table>
        </div>
    </form>
"""

class JSJSB:
    _mid_sblx = ''
    _mid_init_sblx = '<option>%s</option>'
    sql_sblx = 'SELECT DISTINCT SBLX FROM T_SB'

    _mid_init = """
    <tr class="class_tr">
        <td><input type="radio" id="select" name="selradio" /></td>
        <td>%s</td>
        <td id="td_sbbh">%s</td>
        <td>%s</td>
        <td id="td_sblx">%s</td>
        <td>%s</td>
        <td>%s</td>
        <td>%s</td>
        <td>%s</td>
        <td><a href="javascript:void(0);">%s</a></td>
        <td>%s</td>
    </tr>
    """
    
    @cherrypy.expose
    def index(self):#不显示已报废的设备 
        self._mid_sblx = ''
        dl_sblx = ms.ExecQuery(self.sql_sblx)
        for sblx in dl_sblx:
            self._mid_sblx += self._mid_init_sblx % sblx
        sql = """
            SELECT A.SBBH,A.GDZCBH,A.GMRQ,A.SBLX,A.PPXH,A.SBMC,A.WLPZ,B.BM,B.RY FROM T_SB A
            LEFT JOIN (
                SELECT B.RYID,C.BM,C.RY,A.SBBH,A.MAX_BGDH
                FROM(
                    SELECT SBBH,MAX(BGDH) AS MAX_BGDH 
                    FROM T_SBBG GROUP BY SBBH) A
                LEFT JOIN T_SBBG B ON A.MAX_BGDH = B.BGDH
                LEFT JOIN T_BMRY C ON B.RYID = C.ID) B ON A.SBBH = B.SBBH
            WHERE (B.RY IS NULL OR B.RY != '报废')
            ORDER BY A.GMRQ DESC;
        """
                
        dataList = ms.ExecQuery(sql)
        
        _mid = ''
        i = 0
        for sb in dataList:
            i = i + 1
            _mid = _mid + self._mid_init % (i,sb[0],sb[1],datetime.datetime.strftime(sb[2],'%Y-%m-%d'),sb[3],sb[4],sb[5],sb[6],sb[7],sb[8])

        user = cherrypy.session.get('user')
        if user == 'admin':
            return utf8code(head() % '设备信息' + menu() + _js + (_top % self._mid_sblx) + _mid + _end + div_xxtj % (user,len(dataList)) + end())
        else:
            return utf8code(head() % '设备信息' + menu() + _js + _js_noadmin + (_top % self._mid_sblx) + _mid + _end + div_xxtj % (user,len(dataList)) + end())

    @cherrypy.expose
    def delSB(self,sbbh):#添加一个检查，该设备已产生记录的将不能删除
        sql_check = """
            SELECT * FROM T_SBBG
            WHERE SBBH = '%s'
        """
        if ms.ExecQuery(sql_check % sbbh):
            return "no"
        else:
            sql = "DELETE FROM T_SB WHERE SBBH = '%s';" % sbbh
            ms.ExecNonQuery(sql)
            return "ok"

    @cherrypy.expose
    def serchSB(self,cxtj,bfcheckbox=None,selradio=None):
        self._mid_sblx = ''
        dl_sblx = ms.ExecQuery(self.sql_sblx)
        for sblx in dl_sblx:
            if sblx[0] != cxtj:
                self._mid_sblx += self._mid_init_sblx % sblx
            else:
                self._mid_sblx += '<option selected="selected">%s</option>' % sblx
        if bfcheckbox:#显示中包括已报废
            sql = """
                SELECT A.SBBH,A.GDZCBH,A.GMRQ,A.SBLX,A.PPXH,A.SBMC,A.WLPZ,B.BM,B.RY FROM T_SB A
                LEFT JOIN (
                    SELECT B.RYID,C.BM,C.RY,A.SBBH,A.MAX_BGDH
                    FROM(
                        SELECT SBBH,MAX(BGDH) AS MAX_BGDH 
                        FROM T_SBBG GROUP BY SBBH) A
                    LEFT JOIN T_SBBG B ON A.MAX_BGDH = B.BGDH
                    LEFT JOIN T_BMRY C ON B.RYID = C.ID) B ON A.SBBH = B.SBBH
                WHERE A.SBBH LIKE '%""" + cxtj + "%' or " \
                    + "A.GDZCBH LIKE '%" + cxtj + "%' or " \
                    + "A.SBLX LIKE '%" + cxtj + "%' or " \
                    + "A.SBMC LIKE '%" + cxtj + "%' or " \
                    + "A.PPXH LIKE '%" + cxtj + "%' or " \
                    + "A.JBPZ LIKE '%" + cxtj + "%' or " \
                    + "A.WLPZ LIKE '%" + cxtj + "%' or " \
                    + "B.BM LIKE '%" + cxtj + "%' or " \
                    + "B.RY LIKE '%" + cxtj + "%' " \
                + 'ORDER BY A.GMRQ DESC;'
        else:
            sql = """
                SELECT A.SBBH,A.GDZCBH,A.GMRQ,A.SBLX,A.PPXH,A.SBMC,A.WLPZ,B.BM,B.RY FROM T_SB A
                LEFT JOIN (
                    SELECT B.RYID,C.BM,C.RY,A.SBBH,A.MAX_BGDH
                    FROM(
                        SELECT SBBH,MAX(BGDH) AS MAX_BGDH 
                        FROM T_SBBG GROUP BY SBBH) A
                    LEFT JOIN T_SBBG B ON A.MAX_BGDH = B.BGDH
                    LEFT JOIN T_BMRY C ON B.RYID = C.ID) B ON A.SBBH = B.SBBH
                WHERE (B.RY IS NULL OR B.RY != '报废') AND (A.SBBH LIKE '%""" + cxtj + "%' or " \
                    + "A.GDZCBH LIKE '%" + cxtj + "%' or " \
                    + "A.SBLX LIKE '%" + cxtj + "%' or " \
                    + "A.SBMC LIKE '%" + cxtj + "%' or " \
                    + "A.PPXH LIKE '%" + cxtj + "%' or " \
                    + "A.JBPZ LIKE '%" + cxtj + "%' or " \
                    + "A.WLPZ LIKE '%" + cxtj + "%' or " \
                    + "B.BM LIKE '%" + cxtj + "%' or " \
                    + "B.RY LIKE '%" + cxtj + "%') " \
                + 'ORDER BY A.GMRQ DESC;'
        
        dataList = ms.ExecQuery(sql)
        _mid = ''
        i = 0
        for sb in dataList:
            i = i + 1
            _mid = _mid + self._mid_init % (i,sb[0],sb[1],datetime.datetime.strftime(sb[2],'%Y-%m-%d'),sb[3],sb[4],sb[5],sb[6],sb[7],sb[8])

        user = cherrypy.session.get('user')
        if user == 'admin':
            return utf8code(head() % '设备信息' + menu() + _js + (_top % self._mid_sblx) + _mid + _end + div_xxtj % (user,len(dataList)) + end())
        else:
            return utf8code(head() % '设备信息' + menu() + _js + _js_noadmin + (_top % self._mid_sblx) + _mid + _end + div_xxtj % (user,len(dataList)) + end())
    
    @cherrypy.expose
    def showOtherInfo(self,sbbh,sblx):
        showHtml_tmp = ''#设备的其他信息html串
        showHtml = """
            <table width="auto">
                <tr>
                    <th class="class_th" scope="col">基本配置</th>
                    <td colspan="5">%s</td>
                </tr>
                <tr>
                    <th class="class_th" scope="col">随机附件</th>
                    <td colspan="5">%s</td>
                </tr>
                <tr>
                    <th class="class_th" scope="col">设备备注</th>
                    <td colspan="5">%s</td>
                </tr>
            </table>
        """
        sql_show = """
            SELECT JBPZ,SJFJ,BZ
            FROM T_SB
            WHERE SBBH = '%s'
        """
        dataList = ms.ExecQuery(sql_show % sbbh)
        for qyxx in dataList:
            showHtml_tmp += showHtml % (qyxx[0],qyxx[1],qyxx[2])
        #=================================设备变更信息的显示=========================================
        bgjlHtml_s = """      
            <table width="auto">
              <tr>
                <th class="class_th" scope="col">#</th>
                <th class="class_th" scope="col">部门</th>
                <th class="class_th" scope="col">人员</th>
                <th class="class_th" scope="col">变更日期</th>
                <th class="class_th" scope="col">经手人</th>
                <th class="class_th" scope="col">变更备注</th>
              </tr>
        """
        bgjlHtml_e = """
            </table>
        """    
        _mid_tmp = """            
                    <tr>
                        <td>%s</td>
                        <td>%s</td>
                        <td>%s</td>
                        <td>%s</td>
                        <td>%s</td>
                        <td>%s</td>
                    </tr>
                    """
        sql_bgjl = """
            SELECT B.BM,B.RY,A.BGRQ,A.JSR,A.BZ FROM T_SBBG A
            LEFT JOIN T_BMRY B ON A.RYID = B.ID
            WHERE A.SBBH = '%s'
            ORDER BY A.BGRQ
        """        
        dataList_2 = ms.ExecQuery(sql_bgjl % sbbh)
        _mid_2 = ''
        i = 0
        for bgjl in dataList_2:
            i += 1
            _mid_2 += _mid_tmp % (i,bgjl[0],bgjl[1],datetime.datetime.strftime(bgjl[2],'%Y-%m-%d'),bgjl[3],bgjl[4])
        
        showHtml_tmp += bgjlHtml_s + _mid_2 + bgjlHtml_e
        #============================================================================================
        
        return utf8code(showHtml_tmp)
    
    @cherrypy.expose
    def outSB(self):
        sql = """
            SELECT SBBH
                  ,GDZCBH
                  ,SBLX
                  ,SBMC
                  ,PPXH
                  ,GMRQ
                  ,JBPZ
                  ,WLPZ
                  ,SJFJ
                  ,BZ
              FROM T_SB
            ORDER BY SBBH DESC;
            """
        
        dataList = ms.ExecQuery(sql)
        
        sbxx = ''
        for sb in dataList:
            sbxx += sb[0] + "," + sb[1] + "," + sb[2] + "," + sb[3] + "," + sb[4] + "," + datetime.datetime.strftime(sb[5],'%Y-%m-%d') + "," + sb[6] + "," + sb[7] + "," + sb[8] + "," + sb[9] + "\n"
        outFileName = "SBXX" + datetime.datetime.strftime(datetime.datetime.now(),'%Y%m%d%H%I%M%S') +".csv"
        outFile=open(os.path.join(os.getcwd(),'csv',outFileName),"w")
        outFile.write(sbxx)
        outFile.close()
        return print_head % ("操作提示","导出成功!*^_^*") + """<body onLoad='javascript:setTimeout("exit()",10000);'>""" + "<a title='设备信息下载' href='../csv/" + outFileName + "'>" + outFileName + "</a>,正在关闭此页,请稍等......</body>" + print_end
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          